package common.db.repository;

import java.util.ArrayList;
import java.util.List;

/**
 * Class for building dynamic sql queries.
 *
 * For <code>select</code> use:
 * <ul>
 * 		<li>{@link #select()}	for getting instance</li>
 * 		<li>{@link #item} or {@link #tableItem}	to set item</li>
 * 		<li>{@link #from}	to set tables for "from" clause</li>
 * 		<li>{@link #where(String)} 	to set conditions for "where" clause</li>
 * </ul>
 *
 * todo[esadykov]: implement nultitable inserts (???)
 *
 * @author esadykov
 * @since 30.03.2009 12:23:50
 * @version 1
 */
public class SqlQueryBuilder
{
	private enum QueryTypes
	{
		SELECT,
		INSERT,
		UPDATE,
		DELETE
	}

	private class Condition
	{
		protected String condition;

		protected Condition(String condition)
		{
			this.condition = condition;
		}

		protected String str()
		{
			return condition;
		}
	}

	private class FromItem
	{
		protected String item;
		protected String alias;

		protected FromItem(String item, String alias)
		{
			this.item = item;
			this.alias = alias;
		}

		protected String str()
		{
			if (item.isEmpty())
				return null;

			StringBuilder sb = new StringBuilder();

			sb.append(item);

			if (alias != null && !alias.isEmpty())
				sb.append(SPACE).append(alias);

			return sb.toString();
		}
	}

	private class QueryItem
	{
		protected String tableAlias;
		protected String item;
		protected String alias;

		protected QueryItem(String tableAlias, String item, String alias)
		{
			this.tableAlias = tableAlias;
			this.item = item;
			this.alias = alias;
		}

		protected String str()
		{
			if (item.isEmpty())
				return null;

			StringBuilder sb = new StringBuilder();

			if (tableAlias != null && !tableAlias.isEmpty())
				sb.append(tableAlias).append(DOT);

			sb.append(item);

			if (alias != null && !alias.isEmpty())
				sb.append(SPACE).append(alias);

			return sb.toString();
		}
	}

	private class ValueItem
	{
		protected String column;
		protected String value;

		protected ValueItem(String column, String value)
		{
			this.column = column;
			this.value = value;
		}
	}

	private static final String SELECT_KEYWORD = "select";
	private static final String INSERT_KEYWORD = "insert";
	private static final String UPDATE_KEYWORD = "editLabel";
	private static final String DELETE_KEYWORD = "deleteLabel";

	private static final String FROM_KEYWORD = "from";
	private static final String WHERE_KEYWORD = "where";
	private static final String AND_KEYWORD = "and";
	private static final String OR_KEYWORD = "or";
	private static final String VALUES_KEYWORD = "values";
	private static final String SET_KEYWORD = "set";
	private static final String INTO_KEYWORD = "into";

	protected static final char SPACE = ' ';
	protected static final char DOT = '.';
	protected static final char COMMA = ',';
	protected static final char NL = '\n';
	protected static final char TAB = '\t';
	protected static final char QUESTION = '?';
	protected static final char OPEN_PARANTHESES = '(';
	protected static final char CLOSE_PARANTHESES = ')';
	protected static final char EQUAL = '=';
	protected static final char QUOTE = '\'';

	private QueryTypes type;

	private List<QueryItem> queryItems;

	private List<FromItem> fromItems;

	private List<Condition> conditions;

	private String intoTable;

	private List<ValueItem> valueItems;


	private SqlQueryBuilder(QueryTypes type)
	{
		this.type = type;
	}

	public static SqlQueryBuilder select()
	{
		return new SqlQueryBuilder(QueryTypes.SELECT);
	}

	public static SqlQueryBuilder insert()
	{
		return new SqlQueryBuilder(QueryTypes.INSERT);
	}

	public static SqlQueryBuilder update()
	{
		return new SqlQueryBuilder(QueryTypes.UPDATE);
	}

	public static SqlQueryBuilder delete()
	{
		return new SqlQueryBuilder(QueryTypes.DELETE);
	}

	public SqlQueryBuilder into(String table)
	{
		this.intoTable = table;
		return this;
	}

	/**
	 * Alias for {@link SqlQueryBuilder#into}
	 *
	 * @param table 	Table's name for query
	 * @return 			Current instance of {@link SqlQueryBuilder}
	 */
	public SqlQueryBuilder table(String table)
	{
		return into(table);
	}

	public SqlQueryBuilder valueItem(String column)
	{
		return valueItem(column, null);
	}

	public SqlQueryBuilder valueItem(String column, String value)
	{
		if (valueItems == null) valueItems = new ArrayList<ValueItem>();
		this.valueItems.add(new ValueItem(column, value));
		return this;
	}

	public SqlQueryBuilder tableItem(String tableAlias, String item, String alias)
	{
		if (type != QueryTypes.SELECT)
			throw new IllegalStateException();
		if (queryItems == null) {
			queryItems = new ArrayList<QueryItem>();
		}
		queryItems.add(new QueryItem(tableAlias, item, alias));
		return this;
	}

	public SqlQueryBuilder tableItem(String tableAlias, String item)
	{
		return tableItem(tableAlias, item, null);
	}

	public SqlQueryBuilder item(String item, String alias)
	{
		return tableItem(null, item, alias);
	}

	public SqlQueryBuilder item(String item)
	{
		return tableItem(null, item, null);
	}

	public SqlQueryBuilder from(String table, String alias)
	{
		if (type != QueryTypes.SELECT && type != QueryTypes.DELETE)
			throw new IllegalStateException();

		if (fromItems == null) fromItems = new ArrayList<FromItem>();
		fromItems.add(new FromItem(table, alias));
		return this;
	}

	public SqlQueryBuilder from(String table)
	{
		return from(table, null);
	}

	public SqlQueryBuilder where(String condition)
	{
		if (type == QueryTypes.INSERT)
			throw new IllegalStateException();
		if (conditions == null) {
			conditions = new ArrayList<Condition>();
		}
		conditions.add(new Condition(condition));
		return this;
	}

	public String build()
	{
		switch (this.type)
		{
			case SELECT:
				checkSelect();
				return buildSelect();
			case INSERT:
				checkInsert();
				return buildInsert();
			case DELETE:
				checkDelete();
				return buildDelete();
			case UPDATE:
				checkUpdate();
				return buildUpdate();

			default:
				return null;
		}

	}

	private void checkSelect()
	{
		if (queryItems == null || queryItems.isEmpty())
			throw new RuntimeException("SqlQueryBuilder: SELECT check fail, no query items");
		if (fromItems == null || fromItems.isEmpty())
			throw new RuntimeException("SqlQueryBuilder: SELECT check fail, no from items");
	}

	private String buildSelect()
	{
		StringBuilder query = new StringBuilder();

		query.append(SELECT_KEYWORD).append(NL);

		for (int i = 0; i < queryItems.size(); i++)
		{
			QueryItem item = queryItems.get(i);
			query.append(TAB).append(item.str());

			if (i < (queryItems.size() - 1))
				query.append(COMMA);

			query.append(NL);
		}

		query.append(FROM_KEYWORD).append(NL);

		for (int i = 0; i < fromItems.size(); i++)
		{
			FromItem item = fromItems.get(i);
			query.append(TAB).append(item.str());

			if (i < (fromItems.size() - 1))
				query.append(COMMA);

			query.append(NL);
		}

		if (conditions != null && !conditions.isEmpty())
		{
			query.append(WHERE_KEYWORD).append(NL);

			for (int i = 0; i < conditions.size(); i++)
			{
				Condition item = conditions.get(i);

				query.append(TAB);

				if (i > 0)
					query.append(AND_KEYWORD).append(SPACE);

				query.append(item.str());

				query.append(NL);
			}
		}

		return query.toString();

	}

	private void checkInsert()
	{
		if (valueItems == null || valueItems.isEmpty())
			throw new RuntimeException("SqlQueryBuilder: INSERT check fail, no value items");
		if (intoTable == null || intoTable.isEmpty())
			throw new RuntimeException("SqlQueryBuilder: INSERT check fail, no into items");
	}

	private String buildInsert()
	{
		StringBuilder query = new StringBuilder();

		query.append(INSERT_KEYWORD).append(SPACE).append(INTO_KEYWORD).append(SPACE)
				.append(intoTable).append(SPACE).append(OPEN_PARANTHESES).append(NL);

		for (int i = 0; i < valueItems.size(); i++)
		{
			ValueItem valueItem = valueItems.get(i);

			query.append(TAB).append(valueItem.column);

			if (i < (valueItems.size() - 1))
				query.append(COMMA);

			query.append(NL);
		}

		query.append(CLOSE_PARANTHESES)
				.append(SPACE).append(VALUES_KEYWORD).append(SPACE)
				.append(OPEN_PARANTHESES).append(NL);

		for (int i = 0; i < valueItems.size(); i++)
		{
			ValueItem valueItem = valueItems.get(i);

			if (valueItem.value == null || valueItem.value.isEmpty())
				query.append(TAB).append(QUESTION);
			else
				query.append(TAB).append(valueItem.value);

			if (i < (valueItems.size() - 1))
				query.append(COMMA);

			query.append(NL);
		}

		query.append(CLOSE_PARANTHESES).append(NL);

		return query.toString();
	}

	private void checkDelete()
	{
		if (fromItems == null || fromItems.size() != 1)
			throw new RuntimeException("SqlQueryBuilder: DELETE check fail, no from items");
	}

	private String buildDelete()
	{
		StringBuilder query = new StringBuilder();

		query.append(DELETE_KEYWORD).append(SPACE);
		query.append(FROM_KEYWORD).append(SPACE);
		query.append(fromItems.get(0).item).append(NL);

		if (conditions != null && !conditions.isEmpty())
		{
			query.append(WHERE_KEYWORD).append(NL);

			for (int i = 0; i < conditions.size(); i++)
			{
				Condition item = conditions.get(i);

				query.append(TAB);

				if (i > 0)
					query.append(AND_KEYWORD).append(SPACE);

				query.append(item.str());

				query.append(NL);
			}
		}

		return query.toString();
	}

	private void checkUpdate()
	{
		if (valueItems == null || valueItems.isEmpty())
			throw new RuntimeException("SqlQueryBuilder: UPDATE check fail, no value items");
		if (intoTable == null || intoTable.isEmpty())
			throw new RuntimeException("SqlQueryBuilder: UPDATE check fail, no into items");
	}

	private String buildUpdate()
	{
		StringBuilder query = new StringBuilder();

		query.append(UPDATE_KEYWORD).append(SPACE).append(intoTable).append(SPACE).append(SET_KEYWORD).append(NL);

		for (int i = 0; i < valueItems.size(); i++)
		{
			ValueItem valueItem = valueItems.get(i);

			query.append(TAB).append(valueItem.column).append(SPACE).append(EQUAL).append(SPACE);

			if (valueItem.value == null || valueItem.value.isEmpty())
				query.append(QUESTION);
			else
				query.append(valueItem.value);

			if (i < (valueItems.size() - 1))
				query.append(COMMA);

			query.append(NL);
		}

		if (conditions != null && !conditions.isEmpty())
		{
			query.append(WHERE_KEYWORD).append(NL);

			for (int i = 0; i < conditions.size(); i++)
			{
				Condition item = conditions.get(i);

				query.append(TAB);

				if (i > 0)
					query.append(AND_KEYWORD).append(SPACE);

				query.append(item.str());

				query.append(NL);
			}
		}

		return query.toString();
	}

}

